package jinbo.cn.com.util;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.collections.map.CaseInsensitiveMap;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 此方法用于阿里数据库连接池
* @Author:
* @Description:
* @Date:1:28 2018/1/11
* @param  * @param null
*/


@Component("dataSourcePoolDao")
public class DataSourcePoolDao {
	@Autowired
	private DruidDataSource druidDataSource;
	public DruidDataSource getDruidDataSource() {
		return druidDataSource;
	}

	public void setDruidDataSource(DruidDataSource druidDataSource) {
		this.druidDataSource = druidDataSource;
	}
	public Connection getConnection(){
		try {
			return druidDataSource.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	private static Logger log = LoggerFactory.getLogger(DataSourcePoolDao.class);
	/* (non-Javadoc)
	 * @see net.tycmc.bulb.common.dao.ITransactionDAO#batch(java.util.List, java.util.List)
	 */
	public boolean batch(List<String> sqlList, List<Object[]> paramList){
		Connection conn = null;

		//验证参数是否可用
		if(null == sqlList || null == paramList){
			if(log.isDebugEnabled()){
				log.debug("SQL语句列表为空，或者，参数列表为空");
			}
			return false;
		}

		if(sqlList.size() <= 0 || paramList.size() <= 0){
			if(log.isDebugEnabled()){
				log.debug("SQL语句列表为空，或者，参数列表为空");
			}
			return false;
		}

		//SQL和参数表要匹酿
		if(sqlList.size() != paramList.size()){
			if(log.isDebugEnabled()){
				log.debug("SQL语句列表数目 <>参数列表数目");
			}
			return false;
		}
		//取到连接
		conn = this.getConnection();

		try {
			conn.setAutoCommit(false);

			for(int i=0;i<sqlList.size();i++){
				String strSQL = sqlList.get(i);
				Object[] params = paramList.get(i);

				exec(conn, strSQL, params);
			}

			conn.commit();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			log.error("执行SQL语句时发生");

			try {
				if(log.isWarnEnabled()){
					log.warn("进行事物回滚");
				}
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				log.error("事务回滚时发生了错误", e1);
			}
		}finally{
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error("setAutoCommit=true时失败", e);
			}

			ConnHelper.close(conn);
		}

		return false;
	}

	/**
	 * @param conn
	 * @param strSQL
	 * @param params
	 * @return
	 * @throws Exception
	 *
	 * 加入参数校验。当参数校验失败时，抛出错误〿
	 */
	public static boolean exec(Connection conn, String strSQL,
								Object[] params) throws Exception {
		// TODO Auto-generated method stub
		if(null == strSQL){
			throw new Exception("没有给出SQL语句");
		}

		String trimdSQL = strSQL.trim().toLowerCase();
		if(trimdSQL.length() <= 0){
			throw new Exception("没有给出参数列表");
		}

		if(trimdSQL.startsWith("insert")){
			execInsert(conn, strSQL, params);
		}else if(trimdSQL.startsWith("update")){
			execUpdate(conn, strSQL, params);
		}else{
			execDefault(conn, strSQL, params);
			//throw new Exception("事务方法碰到暂时不能支持的sql语句。目前仅支持insert、update语句〿);
		}
		return true;
	}

	private static void execDefault(Connection conn, String strSQL, Object[] params) throws Exception{
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}

			ps.execute();
		} catch (Exception e) {
			log.error("执行以下SQL语句发生错误:"+toString(strSQL, params), e);
			throw e;
		}finally{
			ConnHelper.close(ps);
		}
	}

	private static int execUpdate(Connection conn, String strSQL, Object[] params) throws Exception{
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}

			return ps.executeUpdate();
		} catch (Exception e) {
			log.error("执行以下SQL语句发生错误:"+toString(strSQL, params), e);
			throw e;
		}finally{
			ConnHelper.close(ps);
		}
	}

	private static int execInsert(Connection conn, String strSQL, Object[] params) throws Exception{
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}

			return ps.executeUpdate();
		} catch (Exception e) {
			log.error("执行以下SQL语句发生错误:"+toString(strSQL, params), e);
			throw e;
		}finally{
			ConnHelper.close(ps);
		}
	}

	/* (non-Javadoc)
	 * @see net.tycmc.bulb.common.dao.ITransactionDAO#batch(java.util.List)
	 */
	public boolean batch(List<String> sqlList){
		Connection connection=null;
		Statement stmt = null;

		//棿ߥ参数
		if (sqlList==null || sqlList.size()<=0){
			if(log.isDebugEnabled()){
				log.debug("SQL语句列表为空");
			}
			return false;
		}

		for(String sqlStr : sqlList){
			if (sqlStr == null || "".equals(sqlStr.trim())){
				if(log.isDebugEnabled()){
					log.debug("SQL语句列表中某条SQL语句为空");
				}
				return false;
			}
		}

		connection = this.getConnection();

		//将自动提交方式设为fasle，娀果设置失败关闭连接，返回false〿
		try {
			connection.setAutoCommit(false);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			log.error("setAutoCommit=false时失败", e);
			ConnHelper.close(connection);
			return false;
		}

		try {
			stmt=connection.createStatement();

			//批量添加语句
			for (String sqlStr : sqlList){
				stmt.addBatch(sqlStr);
			}

			//丿̡性提交语叿
			int[] updateCounts = stmt.executeBatch();
			connection.commit();

			return true;
		} catch (Exception ex){
			log.error("执行下面的语句时发生错误＿+sqlList.toString()", ex);//记录错误到日忿

			//批量提交失败，进行事物回滿
			try {
				if(log.isWarnEnabled()){
					log.warn("弿ƾ进行事物回滚");
				}
				connection.rollback();
			} catch (Exception ex1){
				log.error("事务回滚时发生了错误", ex1);
			}
		}finally{
			//setAutoCommit=true
			try {
				connection.setAutoCommit(true);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				log.error("setAutoCommit=true时失败", e);
			}

			//close
			ConnHelper.close(stmt);
			ConnHelper.close(connection);
		}

		return false;
	}

	private static String toString(String strSQL, Object[] params){
		StringBuilder sb = new StringBuilder();
		sb.append(strSQL);
		sb.append("\n");
		for(Object param:params){
			if(param instanceof String){
				sb.append((String)param);
			}else{
				sb.append(param);
			}
			sb.append("\t");
		}
		return sb.toString();
	}
	/* (non-Javadoc)
	 * @see net.tycmc.bulb.common.dao.IBaseDAO#execQuery(java.lang.String, java.lang.Object[])
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String,Object>> execQuery(String strSQL, Object[] params){
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String,Object>> rsList = new ArrayList<Map<String,Object>>(1);

		try {
			ps = conn.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}

			rs = ps.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			while(rs.next()){
				int cc = metaData.getColumnCount();
				Map<String,Object> mapRS =new CaseInsensitiveMap();// new HashMap<String,Object>();
				for(int i=1;i<=cc;i++){
					mapRS.put(metaData.getColumnName(i), rs.getObject(i));
				}
				rsList.add(mapRS);
			}

			return rsList;
		} catch (Exception e) {
			log.error(this.toString(strSQL, params), e);
		}finally{
			ConnHelper.close(rs);
			ConnHelper.close(ps);
			ConnHelper.close(conn);
		}

		return rsList;
	}

	/* (non-Javadoc)
	 * @see net.tycmc.bulb.common.dao.IBaseDAO#execQueryOne(java.lang.String, java.lang.Object[])
	 */
	@SuppressWarnings("unchecked")
	public Map<String,Object> execQueryOne(String strSQL, Object[] params){
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}

			rs = ps.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			if(rs.next()){
				int cc = metaData.getColumnCount();
				Map<String,Object> mapRS = new CaseInsensitiveMap();//new HashMap<String,Object>();
				for(int i=1;i<=cc;i++){
					mapRS.put(metaData.getColumnName(i), rs.getObject(i));
				}
				return mapRS;
			}
		} catch (Exception e) {
			log.error(this.toString(strSQL, params), e);
		}finally{
			ConnHelper.close(rs);
			ConnHelper.close(ps);
			ConnHelper.close(conn);
		}
		return new HashMap<String,Object>();
	}
	/* (non-Javadoc)
 * @see net.tycmc.bulb.common.dao.IBaseDAO#execUpdate(java.lang.String, java.lang.Object[])
 */
	public int execUpdate(String strSQL, Object[] params){
		Connection conn = this.getConnection();
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}

			return ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			log.error(this.toString(strSQL, params), e);
		}finally{
			ConnHelper.close(ps);
			ConnHelper.close(conn);
		}

		return -1;
	}

	/* (non-Javadoc)
	 * @see net.tycmc.bulb.common.dao.IBaseDAO#execInsert(java.lang.String, java.lang.Object[])
	 */
	public int execInsert(String strSQL, Object[] params){
		Connection conn = this.getConnection();
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}

			return ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			log.error(this.toString(strSQL, params), e);
		}finally{
			ConnHelper.close(ps);
			ConnHelper.close(conn);
		}

		return -1;
	}

}
